package cn.gson.CRM.model.dao.order;



import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import cn.gson.CRM.model.entity.Order;
import cn.gson.CRM.model.entity.Order.State;
import cn.gson.CRM.model.vo.CustomerTotalVo;
import cn.gson.CRM.model.vo.ProductTotalVo;

/**
* @author 邓安民:
* @version 创建时间：2018年4月11日 下午2:14:52
* 类说明
*/
public interface OrderDao extends JpaRepository<Order, Long> {
	@Query(value="select o from Order o ")
	public Page<Order> selectByPage(Pageable pageable);
	
	@Query(value="select o from Order o where o.state=?1 and (o.theme like %?2% or o.orderId like %?2% or o.remark like %?2% or o.customer.name like %?2%)")
	public Page<Order> selectByPageWhere(State state,String theme,Pageable pageable);

	
	public Order findByOrderId(String value);
	
	@Query(value="select o from Order o where o.theme like %?1% or o.orderId like %?1% or o.remark like %?1% ")
	public Page<Order> selectByPageTheme(String theme,Pageable pageable);

	
	@Query(value="select o from Order o where o.state=?1")
	public Page<Order> selectByPageState(State state,Pageable pageable);
	
	/**
	 * 统计客户消费金额前15
	 * @return
	 */
	@Query("select new cn.gson.CRM.model.vo.CustomerTotalVo(c.name,sum(o.total)) from Order o inner join o.customer c "
			+ " group by c.name order by sum(o.total) asc")
	public List<CustomerTotalVo> totalByCustomerM(Pageable pageable);

	@Query("select count(*) from Order o where o.state = ?1")
	int findOrderByState(State state);
	
	//本月订单完成数
	@Query("select count(*) from Order o where o.state = 'DONE' and DATE_FORMAT(o.date , '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
	int findOrderByMonth();
	
	//上月订单完成数
	@Query("select count(*) from Order o where o.state = 'DONE' and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( o.date, '%Y%m' ) ) =1 ")
	int findOrderByLastMonth();
	
	@Query(value="SELECT MONTH (date),count(*) FROM order_  WHERE "
			+"YEAR (date) = ?1  and state = ?2 GROUP BY MONTH (date) order by MONTH (date) asc",
			nativeQuery=true)
	List<Object[]>  getOrderData(String year,String state );

	@Query(value="SELECT new cn.gson.CRM.model.vo.ProductTotalVo(g.name,sum(od.subtotal)) FROM OrderDetail od "
			+ "LEFT JOIN Product g on od.product.id = g.id GROUP BY g.id "
			+ "ORDER BY sum(od.subtotal) DESC")
	List<ProductTotalVo> getGoodsPhData(Pageable pg);
}


 